package com.tlc.dao;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.tlc.bean.Book;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class BookDao {
    //调用数据库
    QueryRunner qrunner = new QueryRunner(new ComboPooledDataSource());

    /**
     * 图书详情
     * @param sid
     * @return
     */
    public Book detailsDao(int sid) {
        String sql = "select * from stackroom where sid = ?";
        try {
            Book query = qrunner.query(sql, new BeanHandler<Book>(Book.class), sid);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 添加图书
     * @return
     */
    public int addDao(Book book) {
        String sql = "insert into stackroom values(null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Object[] obj = {book.getSname(), book.getPrice(), book.getAuther(), book.getPhouse(), book.getDtime(), book.getBcount(), book.getNsale(), book.getClassify(), book.getLang(), book.getCover()};
        try {
            int i = qrunner.update(sql, obj);
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询图书
     * @return
     */
    public List<Book> queryDao(String sname, String classify, int startIndex, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from stackroom where 1 = 1");
        //判断传输的数据
        if(!"".equals(sname) && sname != null) {
            sql.append(" and sname  like  '%" + sname + "%'");
        }
        if(!"0".equals(classify) && classify != null && !"".equals(classify)) {
            sql.append(" and classify = '" + classify + "'");
        }

        sql.append(" limit ?,?");

        try {
            List<Book> list = qrunner.query(sql.toString(), new BeanListHandler<Book>(Book.class), startIndex, pageSize);
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 删除图书
     * @param sid
     * @return
     */
    public int deleteDao(int sid) {
        String sql = "delete from stackroom where sid = ?";
        try {
            int i = qrunner.update(sql, sid);
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 修改图书
     * @param book
     * @return
     */
    public int changeDao(Book book) {
        String sql = "update stackroom set sname = ?, price = ?, auther = ?, phouse = ?, dtime = ?, bcount = ?," +
                "nsale = ?, classify = ?, lang = ?, cover = ? where sid = ?";
        Object[] obj = {book.getSname(), book.getPrice(), book.getAuther(), book.getPhouse(), book.getDtime(),
                book.getBcount(), book.getNsale(),book.getClassify(), book.getLang(),book.getCover(), book.getSid()};
        try {
            int i = qrunner.update(sql, obj);
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int infoCount(String sname, String classify) {
        StringBuilder sql = new StringBuilder("select count(*) from stackroom where 1 = 1");
        if(!"".equals(sname) && sname != null) {
            sql.append(" and sname like '%" + sname + "%' ");
        }
        if(!"0".equals(classify) && classify != null && !"".equals(classify)) {
            sql.append(" and classify = " + classify);
        }
        try {
            Long query = qrunner.query(sql.toString(), new ScalarHandler<Long>());
            return query.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
